NetSuite Query
Many of the data types in NetSuite can be queried using the SuiteTalk API. This allows us to use NetSuite data to populate external dropdown lists of customers, vendors, nominal accounts, employees etc.
Refer to the NetSuite online help for more information about the various types of data that NetSuite makes available via the SuiteTalk API. It will be necesssary to understand the tables and their fields in order to construct meaningful queries as described below.
Authenticating
All attempts to use the SuiteTalk API must first be authenticated. This will be an 'AddSecurityOAuth1' step in the data connector. The realm can be obtained from the NetSuite URL, and the consumer key, consumer secret, token ID and secret can all be obtained from the NetSuite integration section as described in the 'NetSuite Overview' section of this help. The default nonce length is 20 and the default signature method is HMACSHA256.
Once you have a working authentication method the same values can be used for all your UniFi to NetSuite integrations.
Querying the Data
In order to query the data you will now need a RestHttpClient step. The URL of this step will be in the format:
https://<realm>.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql
With <realm> replaced by the relevant portion of the URL as described in the NetSuite Overview. The method will always be POST. The content will vary depending on the type of data you are needing to extract but will essentially be in the form of a SQL query describing the fields you want to return and the table you are looking at. So the following example would extract the required columns from the 'Vendor' table where the 'isinactive' field is 'F' indicating the vendor is active.
"q": "SELECT email, entitytitle, id FROM Customer c WHERE c.isinactive = 'F'"
Using Dynamic Parameters
You may want to include fields from the UniFi app as part of your query. E.g. you may want to pull back a list of departments but only include those with a specific 'id' as selected by the user. In this case you can use some Javascript in the content box to narrow down the items selected as follows:
var result = {
"q": "SELECT fullname,id FROM Department WHERE isinactive='F' AND id='"+instance_input?.urlParams?.id +"' "};
return JSON.stringify(result);
This uses the system variable 'instance_input' to look for a parameter of 'id' within the urlParams and returns the full query as a string to be used in the body of the request to NetSuite.
Remember to change the content type to 'Javascript' if you are using this to generate dynamic content in this way
The 'Content Type' is what the body will be marked as when its sent as part of the overall request - this will be text/plain as this is what NetSuite is expecting. This is why the last line of the script above returns the results as a string rather than JSON.
On the 'Advanced' tab ensure that the 'Authorisation Type' is set to 'Inherit Auth from Parent' so that it uses the authorisation credentials obtained in the previous step. You can also add any required headers here.
Finishing the connector
Finally, a data connector must have a finish step. This not only marks the end of the process but also determines the actual data that is being returned. The SuiteTalk API's return data in JSON format in a list of 'items'. This is available as a string in the 'ResponseContent' of the previous step, which we can refer to in Javascript by using the format StepName.ResponseContent. So if the previous step name was 'Vendor' then we can return the data as JSON to the app using the following Javascript code:
JSON.parse(Vendor.ResponseContent).items
This would be entered into the 'Activity Output' field of the 'Finish' action, and the output type set as Javascript'. The finished data connector would then look something like:
You can then switch to the 'Output' tab of the data connector screen and use the 'Text Connector' button to test. As we are expecting a series of items coming back as columns you can set the 'Choose Display Columns' option to 'Yes' and once the columns are returned you can pick which columns you want the user to see by selecting them and moving them into the 'Display' box on the right hand side.
Data for all columns is returned to the app for the item selected, although invisible to the user this can be referred to in formulas that refer to the item using the JSON.parse formula as documented in the formulas section of this help. See the 'Currency Example' of a simple data connector for an example of how to do this.